﻿using System.Collections.Generic;
using Vra.DataAccess.Entities;

namespace Vra.DataAccess
{
	public class WorkDao : CommonDao<Work>, IWorkDao
	{
		/// <summary>
		/// получить выставленные на продажу картины
		/// </summary>
		/// <returns></returns>
		public IList<Work> GetInGallery()
		{
			IList<Work> works = new List<Work>();

			using (var conn = GetConnection())
			{
				conn.Open();

				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText = //С INNER JOIN это смотрится куда понятнее
						"SELECT distinct w.WorkID, ArtistID, Title, Copy, Description " +
						"FROM WORK w " +
						"INNER JOIN TRANS t " +
						"	ON w.WorkID = t.WorkID " +
						"WHERE t.CustomerID IS NULL " +
						"ORDER BY w.WorkID ";

					using (var dataReader = cmd.ExecuteReader())
					{
						while (dataReader.Read())
						{
							works.Add(LoadItem(dataReader));
						}
					}

				}
			}

			return works;
		}

		public IList<Work> SearchWork(string Title, string Artist, string Copy)
		{
			IList<Work> works = new List<Work>();

			using (var conn = GetConnection())
			{
				conn.Open();

				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText =
						"SELECT WorkID, Work.ArtistID, Title, Copy, Description FROM WORK JOIN Artist on WORK.ArtistID = Artist.ArtistID Where Title like @Title AND Artist.Name like @Artist AND Copy like @Copy ";
					cmd.Parameters.AddWithValue("@Title", '%' + Title + '%');
					cmd.Parameters.AddWithValue("@Artist", '%' + Artist + '%');
					cmd.Parameters.AddWithValue("@Copy", '%' + Copy + '%');
					using (var dataReader = cmd.ExecuteReader())
					{
						while (dataReader.Read())
						{
							works.Add(LoadItem(dataReader));
						}
					}

				}
			}

			return works;
		}
	}
}
